Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Datatypes

This chapter provides a reference to Oracle external datatypes used by OCI applications. It also discusses Oracle datatypes and the conversions between internal and external representations that occur when you transfer data between your program and Oracle.

This chapter contains these topics:

- Oracle Datatypes

- Internal Datatypes

- External Datatypes

- Data Conversions

- Typecodes

- Definitions in oratypes.h

  See Also: For detailed information about Oracle internal datatypes, see the Oracle Database SQL Reference

## Oracle Datatypes

One of the main functions of an OCI program is to communicate with a database through an Oracle server. The OCI application may retrieve data from database tables through SQL `SELECT` queries, or it may modify existing data in tables through `INSERT`, `UPDATE`, or `DELETE` statements.

Inside a database, values are stored in columns in tables. Internally, Oracle represents data in particular formats known as internal datatypes. Examples of internal datatypes include `NUMBER`, `CHAR`, and `DATE`.

In general, OCI applications do not work with internal datatype representations of data, but with host language datatypes which are predefined by the language in which they are written. When data is transferred between an OCI client application and a database table, the OCI libraries convert the data between internal datatypes and external datatypes.

External datatypes are host language types that have been defined in the OCI header files. When an OCI application binds input variables, one of the bind parameters is an indication of the external datatype code (or SQLT code) of the variable. Similarly, when output variables are specified in a define call, the external representation of the retrieved data must be specified.

In some cases, external datatypes are similar to internal types. External types provide a convenience for the programmer by making it possible to work with host language types instead of proprietary data formats.

Note: Even though some external types are similar to internal types, an OCI application never binds to internal datatypes. They are discussed here because it can be useful to understand how internal types can map to external types.

The OCI is capable of performing a wide range of datatype conversions when transferring data between Oracle and an OCI application. There are more OCI external datatypes than Oracle internal datatypes. In some cases a single external type maps to an internal type; in other cases multiple external types map to an single internal type.

The many-to-one mappings for some datatypes provide flexibility for the OCI programmer. For example, if you are processing the SQL statement

    SELECT sal FROM emp WHERE empno = :employee_number

and you want the salary to be returned as character data, instead of a binary floating-point format, specify an Oracle external string datatype, such as `VARCHAR2` (code = 1) or `CHAR` (code = 96) for the `dty` parameter in the `OCIDefineByPos()` call for the `sal` column. You also need to declare a string variable in your program and specify its address in the `valuep` parameter.

If you want the salary information to be returned as a binary floating-point value, however, specify the `FLOAT` (code = 4) external datatype. You also need to define a variable of the appropriate type for the `valuep` parameter.

Oracle performs most data conversions transparently. The ability to specify almost any external datatype provides a lot of power for performing specialized tasks. For example, you can input and output `DATE` values in pure binary format, with no character conversion involved, by using the `DATE` external datatype. See the description of the DATE external datatype for more information.

To control data conversion, you must use the appropriate external datatype codes in the bind and define routines. You must tell Oracle where the input or output variables are in your OCI program and their datatypes and lengths.

OCI also supports an additional set of OCI typecodes which are used by Oracle's type management system to represent datatypes of object type attributes. There is a set of predefined constants which can be used to represent these typecodes. The constants each contain the prefix `OCI_TYPECODE`.

In summary, the OCI programmer must be aware of the following different datatypes or data representations:

- Internal Oracle datatypes, which are used by table columns in an Oracle database. These also include datatypes used by PL/SQL which are not used by Oracle columns (for example, indexed table, boolean, record).

  See Also: "Internal Datatypes"

- External OCI datatypes, which are used to specify host language representations of Oracle data.

  See Also: "External Datatypes", and "Using External Datatype Codes"

- `OCI_TYPECODE` values, which are used to Oracle to represent type information for object type attributes.

  See Also: "Typecodes", and "Relationship Between SQLT and OCI_TYPECODE Values"

Information about a column's internal datatype is conveyed to your application in the form of an internal datatype code. Once your application knows what type of data will be returned, it can make appropriate decisions about how to convert and format the output data. The Oracle internal datatype codes are listed in the section "Internal Datatypes".

See Also: For detailed information about Oracle internal datatypes, see the Oracle Database SQL Reference. For information about describing select-list items in a query, see the section "Describing Select-list Items".

### Using External Datatype Codes

An external datatype code indicates to Oracle how a host variable represents data in your program. This determines how the data is converted when returned to output variables in your program, or how it is converted from input (bind) variables to Oracle column values. For example, if you want to convert a `NUMBER` in an Oracle column to a variable-length character array, you specify the VARCHAR2 external datatype code in the `OCIDefineByPos()` call that defines the output variable.

To convert a bind variable to a value in an Oracle column, specify the external datatype code that corresponds to the type of the bind variable. For example, if you want to input a character string such as 02-FEB-65 to a `DATE` column, specify the datatype as a character string and set the length parameter to nine.

It is always the programmer's responsibility to make sure that values are convertible. If you try to insert the string `"`MY BIRTHDAY`"` into a `DATE` column, you will get an error when you execute the statement.

See Also: For a complete list of the external datatypes and datatype codes, see Table 3-2, "External Datatypes and Codes"

## Internal Datatypes

Table 3-1 lists the Oracle internal (also known as built-in) datatypes, along with each type's maximum internal length and datatype code.

Table 3-1 Internal Oracle Datatypes

Internal Oracle Datatype | Maximum Internal Length | Datatype Code
--- | --- | ---
`VARCHAR2, NVARCHAR2` | 4000 bytes | 1
`NUMBER` | 21 bytes | 2
`LONG` | 2^31-1 bytes (2 gigabytes) | 8
`DATE` | 7 bytes | 12
`RAW` | 2000 bytes | 23
`LONG RAW` | 2^31-1 bytes | 24
`ROWID` | 10 bytes | 69
`CHAR, NCHAR` | 2000 bytes | 96
`BINARY_FLOAT` | 4 bytes | 100
`BINARY_DOUBLE` | 8 bytes | 101
User-defined type (object type, `VARRAY`, `Nested Table`) | N/A | 108
`REF` | N/A | 111
`CLOB, NCLOB` | 128 terabytes | 112
`BLOB` | 128 terabytes | 113
`BFILE` | maximum operating system file size or `UB8MAXVAL` | 114
`TIMESTAMP` | 11 bytes | 180
`TIMESTAMP WITH TIME ZONE` | 13 bytes | 181
`INTERVAL YEAR TO MONTH` | 5 bytes | 182
`INTERVAL DAY TO SECOND` | 11 bytes | 183
`UROWID` | 3950 bytes | 208
`TIMESTAMP WITH LOCAL TIME ZONE` | 11 bytes | 231

See Also: For more information about these built-in datatypes, see the Oracle Database SQL Reference.

### LONG, RAW, LONG RAW, VARCHAR2

You can use the piecewise capabilities provided by `OCIBindByName()`, `OCIBindByPos()`, `OCIDefineByPos()`, `OCIStmtGetPieceInfo()` and `OCIStmtSetPieceInfo()` to perform inserts, updates or fetches involving column data of these types.

### Character Strings and Byte Arrays

You can use five Oracle internal datatypes to specify columns that contain characters or arrays of bytes: `CHAR`, `VARCHAR2`, `RAW`, `LONG`, and `LONG` `RAW`.

Note: LOBs can contain characters and `BFILE`s can contain binary data. They are handled differently than other types, so they are not included in this discussion. See Chapter 7, "LOB and BFILE Operations", for more information about these datatypes.

`CHAR`, `VARCHAR2`, and `LONG` columns normally hold character data. `RAW` and `LONG` `RAW` hold bytes that are not interpreted as characters, for example, pixel values in a bit-mapped graphic image. Character data can be transformed when passed through a gateway between networks. Character data passed between machines using different languages, where single characters may be represented by differing numbers of bytes, can be significantly changed in length. Raw data is never converted in this way.

It is the responsibility of the database designer to choose the appropriate Oracle internal datatype for each column in the table. The OCI programmer must be aware of the many possible ways that character and byte-array data can be represented and converted between variables in the OCI program and Oracle tables.

When an array holds characters, the length parameter for the array in an OCI call is always passed in and returned in bytes, not characters.

### UROWID

The Universal `ROWID` (`UROWID`) is a datatype that can store both logical and physical rowids of Oracle tables. Logical rowids are primary key-based logical identifiers for the rows of Index-Organized Tables (IOTs).

To use columns of the `UROWID` datatype, the value of the `COMPATIBLE` initialization parameter must be set to 8.1 or higher.

The following host variables can be bound to Universal ROWIDs:

- `SQLT_CHR` (`VARCHAR2`)

- `SQLT_VCS` (`VARCHAR`)

- `SQLT_STR` (`NULL`-terminated string)

- `SQLT_LVC` (`LONG VARCHAR`)

- `SLQT_AFC` (`CHAR`)

- `SQLT_AVC` (`CHARZ`)

- `SQLT_VST` (OCI String)

- `SQLT_RDD` (`ROWID` descriptor)

### BINARY_FLOAT and BINARY_DOUBLE

The `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes represent single-precision and double-precision floating point values that mostly conform to the IEEE754 standard for Floating Point Arithmetic.

Prior to the addition of these datatypes, all numeric values in an Oracle database were stored in the Oracle `NUMBER` format. These new binary floating point types will not replace Oracle `NUMBER`. Rather, they are alternatives to Oracle `NUMBER` that provide the advantage of using less disk storage.

These internal types are represented by the following codes:

- `SQLT_IBFLOAT` for `BINARY_FLOAT`.

- `SQLT_IBDOUBLE` for `BINARY_DOUBLE`.

All the following host variables can be bound to `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes:

- `SQLT_BFLOAT` (native float)

- `SQLT_BDOUBLE` (native double)

- `SQLT_INT` (integer)

- `SQLT_FLT` (float)

- `SQLT_NUM` (Oracle `NUMBER`)

- `SQLT_UIN` (unsigned)

- `SQLT_VNU` (`VARNUM`)

- `SQLT_CHR` (`VARCHAR2`)

- `SQLT_VCS` (`VARCHAR`)

- `SQLT_STR` (`NULL`-terminated String)

- `SQLT_LVC` (`LONG` `VARCHAR`)

- `SQLT_AFC` (`CHAR`)

- `SQLT_AVC` (`CHARZ`)

- `SQLT_VST` (OCIString)

For best performance, you are advised to use external types `SQLT_BFLOAT` and `SQLT_BDOUBLE` in conjunction with the `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes.

## External Datatypes

Table 3-2 lists datatype codes for external datatypes. For each datatype, the table lists the program variable types for C from or to which Oracle internal data is normally converted.

Table 3-2 External Datatypes and Codes

EXTERNAL DATATYPE | CODE | PROGRAM VARIABLE | OCI DEFINED CONSTANT
--- | --- | --- | ---
`VARCHAR2` | 1 | char[n] | `SQLT_CHR`
`NUMBER` | 2 | unsigned char[21] | `SQLT_NUM`
8-bit signed `INTEGER` | 3 | signed char | `SQLT_INT`
16-bit signed `INTEGER` | 3 | signed short, signed int | `SQLT_INT`
32-bit signed `INTEGER` | 3 | signed int, signed long | `SQLT_INT`
`FLOAT` | 4 | float, double | `SQLT_FLT`
`NULL`-terminated `STRING` | 5 | char[n+1] | `SQLT_STR`
`VARNUM` | 6 | char[22] | `SQLT_VNU`
`LONG` | 8 | char[n] | `SQLT_LNG`
`VARCHAR` | 9 | char[n+sizeof(short integer)] | `SQLT_VCS`
`DATE` | 12 | char[7] | `SQLT_DAT`
`VARRAW` | 15 | unsigned char[n+sizeof(short integer)] | `SQLT_VBI`
native float | 21 | float | `SQLT_BFLOAT`
native double | 22 | double | `SQLT_BDOUBLE`
`RAW` | 23 | unsigned char[n] | `SQLT_BIN`
`LONG RAW` | 24 | unsigned char[n] | `SQLT_LBI`
`UNSIGNED INT` | 68 | unsigned | `SQLT_UIN`
`LONG VARCHAR` | 94 | char[n+sizeof(integer)] | `SQLT_LVC`
`LONG VARRAW` | 95 | unsigned char[n+sizeof(integer)] | `SQLT_LVB`
`CHAR` | 96 | char[n] | `SQLT_AFC`
`CHARZ` | 97 | char[n+1] | `SQLT_AVC`
`ROWID` descriptor | 104 | OCIRowid * | `SQLT_RDD`
NAMED DATATYPE | 108 | struct | `SQLT_NTY`
`REF` | 110 | OCIRef | `SQLT_REF`
Character LOB descriptor | 112 | OCILobLocator (see note 2) | `SQLT_CLOB`
Binary LOB descriptor | 113 | OCILobLocator (see note 2) | `SQLT_BLOB`
Binary FILE descriptor | 114 | OCILobLocator | `SQLT_FILE`
`OCI STRING` type | 155 | OCIString | `SQLT_VST` (see note 1)
`OCI DATE` type | 156 | OCIDate * | `SQLT_ODT` (see note 1)
`ANSI DATE` descriptor | 184 | OCIDateTime * | `SQLT_DATE`
`TIMESTAMP` descriptor | 187 | OCIDateTime * | `SQLT_TIMESTAMP`
`TIMESTAMP WITH TIME ZONE` descriptor | 188 | OCIDateTime * | `SQLT_TIMESTAMP_TZ`
`INTERVAL YEAR TO MONTH` descriptor | 189 | OCIInterval * | `SQLT_INTERVAL_YM`
`INTERVAL DAY TO SECOND` descriptor | 190 | OCIInterval * | `SQLT_INTERVAL_DS`
`TIMESTAMP WITH LOCAL TIME ZONE` descriptor | 232 | OCIDateTime * | `SQLT_TIMESTAMP_LTZ`

Note: Where the length is shown as n, it is a variable, and depends on the requirements of the program (or of the operating system in the case of `ROWID`). For more information on the use of these datatypes, refer to Chapter 11, "Object-Relational Datatypes in OCI". In applications using datatype mappings generated by OTT, CLOBs may be mapped as OCIClobLocator, and BLOBs may be mapped as OCIBlobLocator. For more information, refer to Chapter 14, "Using the Object Type Translator with OCI".

The following three types are internal to PL/SQL and cannot be returned as values by OCI:

- Boolean, `SQLT_BOL`

- Indexed Table, `SQLT_TAB`

- Record, `SQLT_REC`

### VARCHAR2

The `VARCHAR2` datatype is a variable-length string of characters with a maximum length of 4000 bytes.

Note: If you are using Oracle objects, you can work with a special `OCIString` external datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes in OCI" for more information about this datatype.

#### Input

The `value_sz` parameter determines the length in the `OCIBindByName()` or `OCIBindByPos()` call.

If the `value_sz` parameter is greater than zero, Oracle obtains the bind variable value by reading exactly that many bytes, starting at the buffer address in your program. Trailing blanks are stripped, and the resulting value is used in the SQL statement or PL/SQL block. If, in the case of an `INSERT` statement, the resulting value is longer than the defined length of the database column, the `INSERT` fails, and an error is returned.

Note: A trailing `NULL` is not stripped. Variables should be blank-padded but not `NULL`-terminated.

If the `value_sz` parameter is zero, Oracle treats the bind variable as a `NULL`, regardless of its actual content. Of course, a `NULL` must be allowed for the bind variable value in the SQL statement. If you try to insert a `NULL` into a column that has a `NOT` `NULL` integrity constraint, Oracle issues an error, and the row is not inserted.

When the Oracle internal (column) datatype is `NUMBER`, input from a character string that contains the character representation of a number is legal. Input character strings are converted to internal numeric format. If the `VARCHAR2` string contains an illegal conversion character, Oracle returns an error and the value is not inserted into the database.

#### Output

Specify the desired length for the return value in the `value_sz` parameter of the `OCIDefineByPos()` call, or the `value_sz` parameter of `OCIBindByName()` or `OCIBindByPos()` for PL/SQL blocks. If zero is specified for the length, no data is returned.

If you omit the `rlenp` parameter of `OCIDefineByPos()`, returned values are blank-padded to the buffer length, and `NULL`s are returned as a string of blank characters. If `rlenp` is included, returned values are not blank-padded. Instead, their actual lengths are returned in the `rlenp` parameter.

To check if a `NULL` is returned or if character truncation has occurred, include an indicator parameter in the `OCIDefineByPos()` call. Oracle sets the indicator parameter to -1 when a `NULL` is fetched and to the original column length when the returned value is truncated. Otherwise, it is set to zero. If you do not specify an indicator parameter and a `NULL` is selected, the fetch call returns the error code `OCI_SUCCESS_WITH_INFO`. Retrieving diagnostic information on the error will return ORA-1405.

See Also: "Indicator Variables"

### NUMBER

You should not need to use `NUMBER` as an external datatype. If you do use it, Oracle returns numeric values in its internal 21-byte binary format and will expect this format on input. The following discussion is included for completeness only.

Note: If you are using objects in an Oracle database server, you can work with a special `OCINumber` datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes in OCI" for more information about this datatype.

Oracle stores values of the `NUMBER` datatype in a variable-length format. The first byte is the exponent and is followed by 1 to 20 mantissa bytes. The high-order bit of the exponent byte is the sign bit; it is set for positive numbers and it is cleared for negative numbers. The lower 7 bits represent the exponent, which is a base-100 digit with an offset of 65.

To calculate the decimal exponent, add 65 to the base-100 exponent and add another 128 if the number is positive. If the number is negative, you do the same, but subsequently the bits are inverted. For example, -5 has a base-100 exponent = 62 (0x3e). The decimal exponent is thus (~0x3e) -128 - 65 = 0xc1 -128 -65 = 193 -128 -65 = 0.

Each mantissa byte is a base-100 digit, in the range 1..100. For positive numbers, the digit has 1 added to it. So, the mantissa digit for the value 5 is 6. For negative numbers, instead of adding 1, the digit is subtracted from 101. So, the mantissa digit for the number -5 is 96 (101 - 5). Negative numbers have a byte containing 102 appended to the data bytes. However, negative numbers that have 20 mantissa bytes do not have the trailing 102 byte. Because the mantissa digits are stored in base 100, each byte can represent 2 decimal digits. The mantissa is normalized; leading zeroes are not stored.

Up to 20 data bytes can represent the mantissa. However, only 19 are guaranteed to be accurate. The 19 data bytes, each representing a base-100 digit, yield a maximum precision of 38 digits for an Oracle `NUMBER`.

If you specify the datatype code 2 in the `dty` parameter of an `OCIDefineByPos()` call, your program receives numeric data in this Oracle internal format. The output variable should be a 21-byte array to accommodate the largest possible number. Note that only the bytes that represent the number are returned. There is no blank padding or `NULL` termination. If you need to know the number of bytes returned, use the `VARNUM` external datatype instead of `NUMBER`.

See Also: "OCINumber Examples" "VARNUM" for a description of the internal NUMBER format

### INTEGER

The `INTEGER` datatype converts numbers. An external integer is a signed binary number; the size in bytes is system dependent. The host system architecture determines the order of the bytes in the variable. A length specification is required for input and output. If the number being returned from Oracle is not an integer, the fractional part is discarded, and no error or other indication is returned. If the number to be returned exceeds the capacity of a signed integer for the system, Oracle returns an "overflow on conversion" error.

### FLOAT

The `FLOAT` datatype processes numbers that have fractional parts or that exceed the capacity of an integer. The number is represented in the host system's floating-point format. Normally the length is either four or eight bytes. The length specification is required for both input and output.

The internal format of an Oracle number is decimal, and most floating-point implementations are binary; therefore Oracle can represent numbers with greater precision than floating-point representations.

Note: You may receive a round-off error when converting between `FLOAT` and `NUMBER`. Using a `FLOAT` as a bind variable in a query may return an ORA-1403 error. You can avoid this situation by converting the `FLOAT` into a `STRING` and then using `VARCHAR2` or a `NULL`-terminated string for the operation.

### STRING

The `NULL`-terminated `STRING` format behaves like the VARCHAR2 format, except that the string must contain a `NULL` terminator character. This datatype is most useful for C language programs.

#### Input

The string length supplied in the `OCIBindByName()` or `OCIBindByPos()` call limits the scan for the `NULL` terminator. If the `NULL` terminator is not found within the length specified, Oracle issues the error

ORA-01480: trailing `NULL` missing from `STR` bind value

If the length is not specified in the bind call, the OCI uses an implied maximum string length of 4000.

The minimum string length is two bytes. If the first character is a `NULL` terminator and the length is specified as two, a `NULL` is inserted in the column, if permitted. Unlike types `VARCHAR2` and `CHAR`, a string containing all blanks is not treated as a `NULL` on input; it is inserted as is.

Note: Unlike earlier versions of the OCI, in release 8.0 or later, you cannot pass -1 for the string length parameter of a `NULL`-terminated string

#### Output

A `NULL` terminator is placed after the last character returned. If the string exceeds the field length specified, it is truncated and the last character position of the output variable contains the `NULL` terminator.

A `NULL` select-list item returns a `NULL` terminator character in the first character position. An ORA-01405 error is possible, as well.

### VARNUM

The `VARNUM` datatype is like the external `NUMBER` datatype, except that the first byte contains the length of the number representation. This length does not include the length byte itself. Reserve 22 bytes to receive the longest possible `VARNUM`. Set the length byte when you send a `VARNUM` value to Oracle Database.

Table 3-3 shows several examples of the `VARNUM` values returned for numbers in a table.

Table 3-3 VARNUM Examples

Decimal Value | Length Byte | Exponent Byte | Mantissa Bytes | Terminator Byte
--- | --- | --- | --- | ---
0 | 1 | 128 | n/a | n/a
5 | 2 | 193 | 6 | n/a
-5 | 3 | 62 | 96 | 102
2767 | 3 | 194 | 28, 68 | n/a
-2767 | 4 | 61 | 74, 34 | 102
100000 | 2 | 195 | 11 | n/a
1234567 | 5 | 196 | 2, 24, 46, 68 | n/a

### LONG

The `LONG` datatype stores character strings longer than 4000 bytes. You can store up to two gigabytes (2^31-1 bytes) in a `LONG` column. Columns of this type are used only for storage and retrieval of long strings. They cannot be used in functions, expressions, or `WHERE` clauses. `LONG` column values are generally converted to and from character strings.

Do not create tables with `LONG` columns. Use `LOB` columns (`CLOB`, `NCLOB`, or `BLOB`) instead. `LONG` columns are supported only for backward compatibility.

Oracle also recommends that you convert existing `LONG` columns to `LOB` columns. `LOB` columns are subject to far fewer restrictions than `LONG` columns. Furthermore, `LOB` functionality is enhanced in every release, but `LONG` functionality has been static for several releases.

### VARCHAR

The `VARCHAR` datatype stores character strings of varying length. The first two bytes contain the length of the character string, and the remaining bytes contain the string. The specified length of the string in a bind or a define call must include the two length bytes, so the largest `VARCHAR` string that can be received or sent is 65533 bytes long, not 65535.

### DATE

The `DATE` datatype can update, insert, or retrieve a date value using the Oracle internal date binary format. A date in binary format contains seven bytes, as shown in Table 3-4.

Table 3-4 Format of the DATE Datatype

Byte | 1 | 2 | 3 | 4 | 5 | 6 | 7
--- | --- | --- | --- | --- | --- | --- | ---
Meaning | Century | Year | Month | Day | Hour | Minute | Second
Example (for 30-NOV-1992, 3:17 PM) | 119 | 192 | 11 | 30 | 16 | 18 | 1

The century and year bytes (bytes 1 and 2) are in excess-100 notation. The first byte stores the value of the year, which is 1992, as an integer, divided by 100, giving 119 in excess-100 notation. The second byte stores year modulo 100, giving 192. Dates Before Common Era (BCE) are less than 100. The era begins on 01-JAN-4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess-1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).

When you enter a date in binary format using the `DATE` external datatype, the database does not do consistency or range checking. All data in this format must be carefully validated before input.

Note: There is little need to use the Oracle external `DATE` datatype in ordinary database operations. It is much more convenient to convert `DATE` into character format, because the program usually deals with data in a character format, such as DD-MON-YY.

When a `DATE` column is converted to a character string in your program, it is returned using the default format mask for your session, or as specified in the `INIT.ORA` file.

See Also: If you are using objects in an Oracle database, you can work with a special `OCIDate` datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes in OCI" for more information about this datatype. For information about `DATETIME` and `INTERVAL` datatypes, refer to "Datetime and Interval Datatype Descriptors".

### RAW

The `RAW` datatype is used for binary data or byte strings that are not to be interpreted by Oracle, for example, to store graphics character sequences. The maximum length of a `RAW` column is 2000 bytes.

See Also: Oracle Database SQL Reference.

When `RAW` data in an Oracle table is converted to a character string in a program, the data is represented in hexadecimal character code. Each byte of the `RAW` data is returned as two characters that indicate the value of the byte, from '00' to 'FF'. If you want to input a character string in your program to a `RAW` column in an Oracle table, you must code the data in the character string using this hexadecimal code.

You can use the piecewise capabilities provided by `OCIDefineByPos()`, `OCIBindByName()`, `OCIBindByPos()`, `OCIStmtGetPieceInfo()`, and `OCIStmtSetPieceInfo()` to perform inserts, updates, or fetches involving `RAW` (or `LONG` `RAW`) columns.

See Also: If you are using objects in an Oracle database, you can work with a special `OCIRaw` datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes in OCI" for more information about this datatype.

### VARRAW

The `VARRAW` datatype is similar to the `RAW` datatype. However, the first two bytes contain the length of the data. The specified length of the string in a bind or a define call must include the two length bytes, so the largest `VARRAW` string that can be received or sent is 65533 bytes, not 65535. For converting longer strings, use the `LONG` `VARRAW` external datatype.

### LONG RAW

The `LONG` `RAW` datatype is similar to the `RAW` datatype, except that it stores raw data with a length up to two gigabytes (2^31-1 bytes).

### UNSIGNED

The `UNSIGNED` datatype is used for unsigned binary integers. The size in bytes is system dependent. The host system architecture determines the order of the bytes in a word. A length specification is required for input and output. If the number being output from Oracle is not an integer, the fractional part is discarded, and no error or other indication is returned. If the number to be returned exceeds the capacity of an unsigned integer for the system, Oracle returns an "overflow on conversion" error.

### LONG VARCHAR

The `LONG` `VARCHAR` datatype stores data from and into an Oracle `LONG` column. The first four bytes of a `LONG` `VARCHAR` contain the length of the item. So, the maximum length of a stored item is 2^31-5 bytes.

### LONG VARRAW

The `LONG` `VARRAW` datatype is used to store data from and into an Oracle `LONG` `RAW` column. The length is contained in the first four bytes. The maximum length is 2^31-5 bytes.

### CHAR

The `CHAR` datatype is a string of characters, with a maximum length of 2000. `CHAR` strings are compared using blank-padded comparison semantics.

See Also: Oracle Database SQL Reference

#### Input

The length is determined by the `value_sz` parameter in the `OCIBindByName()` or `OCIBindByPos()` call.

Note: The entire contents of the buffer (`value_sz` chars) is passed to the database, including any trailing blanks or `NULL`s

If the `value_sz` parameter is zero, Oracle treats the bind variable as a `NULL`, regardless of its actual content. Of course, a `NULL` must be allowed for the bind variable value in the SQL statement. If you try to insert a `NULL` into a column that has a `NOT` `NULL` integrity constraint, Oracle issues an error and does not insert the row.

Negative values for the `value_sz` parameter are not allowed for CHARs.

When the Oracle internal (column) datatype is `NUMBER`, input from a character string that contains the character representation of a number is legal. Input character strings are converted to internal numeric format. If the `CHAR` string contains an illegal conversion character, Oracle returns an error and does not insert the value. Number conversion follows the conventions established by Globalization Support settings for your system. For example, your system might be configured to recognize a comma (,) rather than a period (.) as the decimal point.

#### Output

Specify the desired length for the return value in the `value_sz` parameter of the `OCIDefineByPos()` call. If zero is specified for the length, no data is returned.

If you omit the `rlenp` parameter of `OCIDefineByPos()`, returned values are blank padded to the buffer length, and `NULL`s are returned as a string of blank characters. If `rlenp` is included, returned values are not blank padded. Instead, their actual lengths are returned in the `rlenp` parameter.

To check whether a `NULL` is returned or if character truncation has occurred, include an indicator parameter or array of indicator parameters in the `OCIDefineByPos()` call. An indicator parameter is set to -1 when a `NULL` is fetched and to the original column length when the returned value is truncated. Otherwise, it is set to zero. If you do not specify an indicator parameter and a `NULL` is selected, the fetch call returns an ORA-01405 error.

See Also: "Indicator Variables"

You can also request output to a character string from an internal `NUMBER` datatype. Number conversion follows the conventions established by the Globalization Support settings for your system. For example, your system might use a comma (,) rather than a period (.) as the decimal point.

### CHARZ

The `CHARZ` external datatype is similar to the `CHAR` datatype, except that the string must be `NULL`-terminated on input, and Oracle places a `NULL`-terminator character at the end of the string on output. The `NULL` terminator serves only to delimit the string on input or output; it is not part of the data in the table.

On input, the length parameter must indicate the exact length, including the `NULL` terminator. For example, if an array in C is declared as

    char my_num[] = "123.45";

then the length parameter when you bind `my_num` must be seven. Any other value would return an error for this example.

The following new external datatypes were introduced with or after release 8.0. These datatypes are not supported when you connect to an Oracle release 7 server.

Note: Both internal and external datatypes have Oracle-defined constant values, such as `SQLT_NTY`, `SQLT_REF`, corresponding to their datatype codes. Although the constants are not listed for all of the types in this chapter, they are used in this section when discussing new Oracle datatypes. The datatype constants are also used in other chapters of this guide when referring to these new types.

### Named Datatypes: Object, VARRAY, Nested Table

Named datatypes are user-defined types which are specified with the `CREATE` `TYPE` command in SQL. Examples include object types, varrays, and nested tables. In the OCI, named datatype refers to a host language representation of the type. The `SQLT_NTY` datatype code is used when binding or defining named datatypes.

In a C application, named datatypes are represented as C structs. These structs can be generated from types stored in the database by using the Object Type Translator. These types correspond to `OCI_TYPECODE_OBJECT`.

See Also: For more information about working with named datatypes in the OCI, see Chapter 11, "Object-Relational Datatypes in OCI". For information about how named datatypes are represented as C structs, refer to Chapter 14, "Using the Object Type Translator with OCI".

### REF

This is a reference to a named datatype. The C language representation of a `REF` is a variable declared to be of type `OCIRef *`. The `SQLT_REF` datatype code is used when binding or defining REFs.

Access to REFs is only possible when an OCI application has been initialized in object mode. When REFs are retrieved from the server, they are stored in the client-side object cache.

To allocate a `REF` for use in your application, you should declare a variable to be a pointer to a `REF`, and then call `OCIObjectNew()`, passing `OCI_TYPECODE_REF` as the `typecode` parameter.

See Also: For more information about working with REFs in the OCI, see Chapter 13, "Object Advanced Topics in OCI"

### ROWID Descriptor

The `ROWID` datatype identifies a particular row in a database table. `ROWID` can be a select-list item in a query, such as:

    SELECT ROWID, ename, empno FROM emp

In this case, you can use the returned `ROWID` in further `DELETE` statements.

If you are performing a `SELECT` for `UPDATE`, the `ROWID` is implicitly returned. This `ROWID` can be read into a user-allocated `ROWID` descriptor using `OCIAttrGet()` on the statement handle and used in a subsequent `UPDATE` statement. The prefetch operation fetches all `ROWIDs` on a `SELECT` for `UPDATE`; use prefetching and then a single row fetch.

You access rowids through the use of a `ROWID` descriptor, which you can use as a bind or define variable.

See Also: "OCI Descriptors" and "Positioned Updates and Deletes" for more information about the use of the `ROWID` descriptor

### LOB Descriptor

A LOB (Large Object) stores binary or character data up to 128 terabytes in length. Binary data is stored in a `BLOB` (Binary LOB), and character data is stored in a `CLOB` (Character LOB) or `NCLOB` (National Character LOB).

LOB values may or may not be stored inline with other row data in the database. In either case, LOBs have the full transactional support of the database server. A database table stores a LOB locator that points to the LOB value, which may be in a different storage space.

When an OCI application issues a SQL query which includes a LOB column or attribute in its select-list, fetching the result(s) of the query returns the locator, rather than the actual LOB value. In OCI, the LOB locator maps to a variable of type `OCILobLocator`.

Note: Depending on your application, you may or may not want to use LOB locators. You can use the data interface for LOBs, which does not require LOB locators. In this interface, you can bind or define character data for `CLOB` columns or `RAW` data for `BLOB` columns.

See Also: For more information about descriptors, including the LOB locator, see the section "OCI Descriptors" For more information about LOBs refer to the Oracle Database SQL Reference and the Oracle Database Application Developer's Guide - Large Objects. "Binding LOB Data" "Defining LOB Data"

The OCI functions for LOBs take a LOB locator as one of their arguments. The OCI functions assume that the locator has already been created, whether or not the LOB to which it points contains data.

Bind and define operations are performed on the LOB locator, which is allocated with the `OCIDescriptorAlloc()` function.

The locator is always fetched first using SQL or `OCIObjectPin()`, and then operations are performed using the locator. The OCI functions never take the actual LOB value as a parameter.

See Also: For more information about OCI LOB functions, see Chapter 7, "LOB and BFILE Operations"

The datatype codes available for binding or defining LOBs are:

- `SQLT_BLOB` - a binary LOB datatype.

- `SQLT_CLOB` - a character LOB datatype.

The `NCLOB` is a special type of `CLOB` with the following requirements:

- To write into or read from an `NCLOB`, the user must set the character set form (`csfrm`) parameter to be SQLCS_NCHAR.

- The amount (`amtp`) parameter in calls involving `CLOB`s and `NCLOB`s is always interpreted in terms of characters, rather than bytes, for fixed-width character sets.

  See Also: "LOB and BFILE Functions in OCI"

#### BFILE

Oracle supports access to binary files, or `BFILE`s. The `BFILE` datatype provides access to LOBs that are stored in file systems outside an Oracle database.

A `BFILE` column or attribute stores a file LOB locator, which serves as a pointer to a binary file on the server's file system. The locator maintains the directory object and the filename. The maximum size of a `BFILE` is the smaller of the operating system maximum file size or `UB8MAXVAL`.

Binary file LOBs do not participate in transactions. Rather, the underlying operating system provides file integrity and durability.

The database administrator must ensure that the file exists and that Oracle processes have operating system read permissions on the file.

The `BFILE` datatype allows read-only support of large binary files; you cannot modify a file through Oracle. Oracle provides APIs to access file data.

The datatype code available for binding or defining `BFILE`s is:

- `SQLT_BFILE` - a binary FILE LOB datatype

  See Also: For more information about directory aliases, refer to the Oracle Database Application Developer's Guide - Large Objects

#### BLOB

The `BLOB` datatype stores unstructured binary large objects. `BLOB`s can be thought of as bit streams with no character set semantics. `BLOB`s can store up to 128 terabytes of binary data.

`BLOB`s have full transactional support; changes made through the OCI participate fully in the transaction. The `BLOB` value manipulations can be committed or rolled back. You cannot save a `BLOB` locator in a variable in one transaction and then use it in another transaction or session.

#### CLOB

The `CLOB` datatype stores fixed- or variable-width character data. `CLOB`s can store up to 128 terabytes of character data.

`CLOB`s have full transactional support; changes made through the OCI participate fully in the transaction. The `CLOB` value manipulations can be committed or rolled back. You cannot save a `CLOB` locator in a variable in one transaction and then use it in another transaction or session.

#### NCLOB

An `NCLOB` is a national character version of a `CLOB`. It stores fixed-width, single-byte or multibyte national character set (`NCHAR`) data, or variable-width character set data. `NCLOB`s can store up to 128 terabytes of character text data.

`NCLOB`s have full transactional support; changes made through the OCI participate fully in the transaction. `NCLOB` value manipulations can be committed or rolled back. You cannot save a `NCLOB` locator in a variable in one transaction and then use it in another transaction or session.

### Datetime and Interval Datatype Descriptors

The datetime and interval datatype descriptors are briefly summarized here.

See Also: Oracle Database SQL Reference

#### ANSI DATE

The `ANSI` `DATE` is based on the `DATE`, but contains no time portion. It also has no time zone. `ANSI` `DATE` follows the `ANSI` specification for the `DATE` datatype. When assigning an `ANSI` `DATE` to a `DATE` or a timestamp datatype, the time portion of the Oracle `DATE` and the timestamp are set to zero. When assigning a `DATE` or a timestamp to an `ANSI` `DATE`, the time portion is ignored.

You are encouraged to instead use the `TIMESTAMP` datatype which contains both date and time.

#### TIMESTAMP

The `TIMESTAMP` datatype is an extension of the `DATE` datatype. It stores the year, month, and day of the `DATE` datatype, plus the hour, minute, and second values. It has no time zone. The `TIMESTAMP` datatype has the form:

    TIMESTAMP(fractional_seconds_precision)

where the optional `fractional_seconds_precision` specifies the number of digits in the fractional part of the `SECOND` datetime field and can be a number in the range 0 to 9. The default is 6.

#### TIMESTAMP WITH TIME ZONE

`TIMESTAMP` `WITH` `TIME` `ZONE` (`TSTZ`) is a variant of `TIMESTAMP` that includes an explicit time zone displacement in its value. The time zone displacement is the difference in hours and minutes between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). The `TIMESTAMP` `WITH` `TIME` `ZONE` datatype has the form:

    TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE

where `fractional_seconds_precision` optionally specifies the number of digits in the fractional part of the `SECOND` datetime field, and can be a number in the range 0 to 9. The default is 6.

Two `TIMESTAMP` `WITH` `TIME` `ZONE` values are considered identical if they represent the same instant in UTC, regardless of the `TIME` `ZONE` offsets stored in the data.

#### TIMESTAMP WITH LOCAL TIME ZONE

`TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` (`TSLTZ`) is another variant of `TIMESTAMP` that includes a time zone displacement in its value. Storage is in the same format as for `TIMESTAMP`. This type differs from `TIMESTAMP` `WITH` `TIME` `ZONE` in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When retrieving the data, Oracle returns it in your local session time zone.

The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). The `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` datatype has the form:

    TIMESTAMP(fractional_seconds_precision) WITH LOCAL TIME ZONE

where `fractional_seconds_precision` optionally specifies the number of digits in the fractional part of the `SECOND` datetime field and can be a number in the range 0 to 9. The default is 6.

#### INTERVAL YEAR TO MONTH

`INTERVAL` `YEAR` `TO` `MONTH` stores a period of time using the `YEAR` and `MONTH` datetime fields. The `INTERVAL` `YEAR` `TO` `MONTH` datatype has the form:

    INTERVAL YEAR(year_precision) TO MONTH

where the optional `year_precision` is the number of digits in the `YEAR` datetime field. The default value of `year_precision` is 2.

#### INTERVAL DAY TO SECOND

`INTERVAL` `DAY` `TO` `SECOND` stores a period of time in terms of days, hours, minutes, and seconds. The `INTERVAL` `DAY` `TO` `SECOND` datatype has the form:

    INTERVAL DAY (day_precision) TO SECOND(fractional_seconds_precision)

where:

- `day_precision` is the optional number of digits in the `DAY` datetime field. It is optional. Accepted values are 0 to 9. The default is 2.

`fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values are 0 to 9. The default is 6.

#### Avoiding Unexpected Results Using Datetime

Note: To avoid unexpected results in your DML operations on datetime data, you can verify the database and session time zones by querying the built-in SQL functions `DBTIMEZONE` and `SESSIONTIMEZONE`. If the time zones have not been set manually, Oracle uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, Oracle uses UTC as the default value.

### Native Float and Native Double

The native float (`SQLT_BFLOAT`) and native double (`SQLT_BDOUBLE`) datatypes represent the single-precision and double-precision floating point values. They are represented natively, that is, in the host system's floating point format.

Note that these new external types were added to externally represent the `BINARY_FLOAT` and `BINARY_DOUBLE` internal datatypes. Thus, performance for the new internal types will be best when used in conjunction with external types native float and native double respectively. This draws a clear distinction between the existing representation of floating point values (`SQLT_FLT`) and these new types.

### C Object-Relational Datatype Mappings

OCI supports Oracle-defined C datatypes for mapping user-defined datatypes to C representations (for example, `OCINumber`, `OCIArray`). OCI provides a set of calls to operate on these datatypes, and to use these datatypes in bind and define operations, in conjunction with OCI external datatypes.

See Also: For information on using these Oracle-defined C datatypes, refer to Chapter 11, "Object-Relational Datatypes in OCI"

## Data Conversions

Table 3-5 show the supported conversions from internal datatypes to external datatypes, and from external datatypes into internal column representations, for all datatypes available through release 7.3. Information about data conversions for datatypes newer than release 7.3 is listed here:

- REFs stored in the database are converted to `SQLT_REF` on output.

- `SQLT_REF` is converted to the internal representation of REFs on input.

- Named datatypes stored in the database can be converted to `SQLT_NTY` (and represented by a C struct in the application) on output.

- `SQLT_NTY` (represented by a C struct in an application) is converted to the internal representation of the corresponding type on input.

LOBs are shown in Table 3-6, because of the width limitation.

See Also: For information about `OCIString`, `OCINumber`, and other new datatypes, refer to Chapter 11, "Object-Relational Datatypes in OCI"

Table 3-5 Data Conversions

- | INTERNAL DATATYPES-> | - | - | - | - | - | - | - | -
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
EXTERNALDATATYPES | VARCHAR2 | NUMBER | LONG | ROWID | UROWID | DATE | RAW | LONG RAW | CHAR
`VARCHAR2` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I/O(3) | -
`NUMBER` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`INTEGER` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`FLOAT` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`STRING` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I/O(3, 5) | I/O
`VARNUM` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`DECIMAL` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`LONG` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I/O(3, 5) | I/O
`VARCHAR` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I/O(3, 5) | I/O
`DATE` | I/O | - | I | - | - | I/O | - | - | I/O
`VARRAW` | I/O(6) | - | I(5, 6) | - | - | - | I/O | I/O | I/O(6)
`RAW` | I/O(6) | - | I(5, 6) | - | - | - | I/O | I/O | I/O(6)
`LONG RAW` | O(6) | - | I(5, 6) | - | - | - | I/O | I/O | O(6)
`UNSIGNED` | I/O(4) | I/O | I | - | - | - | - | - | I/O(4)
`LONG VARCHAR` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I/O(3, 5) | I/O
`LONG VARRAW` | I/O(6) | - | I(5, 6) | - | - | - | I/O | I/O | I/O(6)
`CHAR` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I(3) | I/O
`CHARZ` | I/O | I/O | I/O | I/O(1) | I/O(1) | I/O(2) | I/O(3) | I(3) | I/O
`ROWID` descriptor | I(1) | - | - | I/O | I/O | - | - | - | I(1)

Legend:

I = Conversion valid for input only

O = Conversion valid for output only

I/O = Conversion valid for input or output

Notes:

(1) For input, host string must be in Oracle `ROWID/UROWID` format.

On output, column value is returned in Oracle `ROWID/UROWID` format.

(2) For input, host string must be in the Oracle `DATE` character format.

On output, column value is returned in Oracle `DATE` format.

(3) For input, host string must be in hex format.

On output, column value is returned in hex format.

(4) For output, column value must represent a valid number.

(5) Length must be less than or equal to 2000.

(6) On input, column value is stored in hex format.

On output, column value must be in hex format.

### Data Conversions for LOB Datatype Descriptors

These are the data conversions for `LOB`s:

Table 3-6 Data Conversions for LOBs

EXTERNAL DATATYPES | INTERNAL CLOB | INTERNAL BLOB
--- | --- | ---
`VARCHAR` | I/O |
`CHAR` | I/O |
`LONG` | I/O |
`LONG VARCHAR` | I/O |
`RAW` |  | I/O
`VARRAW` |  | I/O
`LONG RAW` |  | I/O
`LONG VARRAW` |  | I/O

### Data Conversions for Datetime and Interval Datatypes

You can also use one of the character datatypes for the host variable used in a fetch or insert operation from or to a datetime or interval column. Oracle will do the conversion between the character datatype and datetime/interval datatype for you.

Table 3-7 Data Conversion for Datetime and Interval Types

External Types/Internal Types | VARCHAR,CHAR | DATE | TS | TSTZ | TSLTZ | INTERVAL YEAR TO MONTH | INTERVAL DAY TO SECOND
--- | --- | --- | --- | --- | --- | --- | ---
`VARCHAR2, CHAR` | I/O | I/O | I/O | I/O | I/O | I/O | I/O
`DATE` | I/O | I/O | I/O | I/O | I/O | - | -
`OCI DATE` | I/O | I/O | I/O | I/O | I/O | - | -
`ANSI DATE` | I/O | I/O | I/O | I/O | I/O | - | -
`TIMESTAMP (TS)` | I/O | I/O | I/O | I/O | I/O | - | -
`TIMESTAMP WITH TIME ZONE (TSTZ)` | I/O | I/O | I/O | I/O | I/O | - | -
`TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)` | I/O | I/O | I/O | I/O | I/O | - | -
`INTERVAL YEAR TO MONTH` | I/O | - | - | - | - | I/O | -
`INTERVAL DAY TO SECOND` | I/O | - | - | - | - | - | I/O

#### Assignment Notes

When assigning a source with time zone to a target without a time zone, the time zone portion of the source is ignored. On assigning a source without a time zone to a target with a time zone, the time zone of the target is set to the session's default time zone

When assigning an Oracle `DATE` to a `TIMESTAMP`, the `TIME` portion of the `DATE` is copied over to the `TIMESTAMP`. When assigning a `TIMESTAMP` to Oracle `DATE`, the `TIME` portion of the result `DATE` is set to zero. This is done to encourage upgrading of Oracle `DATE` to `ANSI` compliant `DATETIME` datatypes

When assigning an `ANSI` `DATE` to an Oracle `DATE` or a `TIMESTAMP`, the `TIME` portion of the Oracle `DATE` and the `TIMESTAMP` are set to zero. When assigning an Oracle `DATE` or a `TIMESTAMP` to an ANSI `DATE`, the `TIME` portion is ignored

When assigning a `DATETIME` to a character string, the `DATETIME` is converted using the session's default `DATETIME` format. When assigning a character string to a `DATETIME`, the string must contain a valid `DATETIME` value based on the session's default `DATETIME` format

`When assigning a character string to an INTERVAL``, the character string must be a valid INTERVAL` character format.

#### Data Conversion Notes for Datetime and Interval Types

(1) When converting from TSLTZ to CHAR, DATE, TIMESTAMP, and TSTZ, the value will be adjusted to the session time zone.

(2) When converting from `CHAR`, `DATE`, and `TIMESTAMP` to `TSLTZ`, the session time zone will be stored in memory.

(3) When assigning `TSLTZ` to `ANSI` `DATE`, the time portion will be zero.

(4) When converting from `TSTZ`, the time zone which the time stamp is in will be stored in memory.

(5) When assigning a character string to an interval, the character string must be a valid interval character format.

### Datetime and Date Upgrading Rules

OCI has full forward and backward compatibility between a client application and the database server as far as the datetime and date columns are concerned.

#### Pre-9.0 Client with 9.0 or Later Server

The only datetime datatype available to a pre-9.0 application is the `DATE` datatype, `SQLT_DAT`. When a pre-9.0 client that defined a buffer as `SQLT_DAT`, tries to obtain data from a `TSLTZ` column, then only the date portion of the value will be returned to the client.

#### Pre-9.0 Server with 9.0 or Later Client

In this case the new client can have a bind or define buffer of type `SQLT_TIMESTAMP_LTZ`. The following compatibilities are maintained in this case.

If any client application tries to insert a `SQLT_TIMESTAMP_LTZ` (or any of the new datetime datatypes) into a `DATE` column, an error will be issued since there is potential data loss in this situation.

When a client has an OUT bind or a define buffer that is of datatype `SQLT_TIMESTAMP_LTZ` and the underlying server side SQL buffer or column is of `DATE` type, then the session time zone is assigned.

### Data Conversion for BINARY_FLOAT and BINARY_DOUBLE in OCI

Table 3-8 shows the supported conversions between internal numerical datatypes and all relevant external types. An (I) implies that the conversion is valid for input only (binds), and (O) implies that the conversion is valid for output only (defines), while an (I/O) implies that the conversion is valid for input as well as output (binds and defines).

Table 3-8 Data Conversion for External Datatypes to Internal Numerical Datatypes

External Types/Internal Types | BINARY_FLOAT | BINARY_DOUBLE
--- | --- | ---
`VARCHAR` | I/O | I/O
`VARCHAR2` | I/O | I/O
`NUMBER` | I/O | I/O
`INTEGER` | I/O | I/O
`FLOAT` | I/O | I/O
`STRING` | I/O | I/O
`VARNUM` | I/O | I/O
`LONG` | I/O | I/O
`UNSIGNED INT` | I/O | I/O
`LONG VARCHAR` | I/O | I/O
`CHAR` | I/O | I/O
`BINARY_FLOAT` | I/O | I/O
`BINARY_DOUBLE` | I/O | I/O

Table 3-9 shows the supported conversions between all relevant internal types and numerical external types. An (I) implies that the conversion is valid for input only (only for binds), and (O) implies that the conversion is valid for output only (only for defines), while an (I/O) implies that the conversion is valid for input as well as output (binds and defines).

Table 3-9 Data Conversions for Internal to External Numerical Datatypes

Internal Types/External Types | Native Float | Native Double
--- | --- | ---
VARCHAR2 | I/O | I/O
NUMBER | I/O | I/O
LONG | I | I
CHAR | I/O | I/O
BINARY_FLOAT | I/O | I/O
BINARY_DOUBLE | I/O | I/O

## Typecodes

There is a unique typecode associated with each Oracle type, whether scalar, collection, reference, or object type. This typecode identifies the type, and is used by Oracle to manage information about object type attributes. This typecode system is designed to be generic and extensible, and is not tied to a direct one-to-one mapping to Oracle datatypes. Consider the following SQL statements:

    CREATE TYPE my_type AS OBJECT
    ( attr1    NUMBER,
      attr2    INTEGER,
      attr3    SMALLINT);

    CREATE TABLE my_table AS TABLE OF my_type;

These statements create an object type and an object table. When it is created, `my_table` will have three columns, all of which are of Oracle `NUMBER` type, because `SMALLINT` and `INTEGER` map internally to `NUMBER`. The internal representation of the attributes of `my_type`, however, maintains the distinction between the datatypes of the three attributes: `attr1` is `OCI_TYPECODE_NUMBER`, `attr2` is `OCI_TYPECODE_INTEGER`, and `attr3` is `OCI_TYPECODE_SMALLINT`. If an application describes `my_type`, these typecodes are returned.

`OCITypeCode` is the C datatype of the typecode. The typecode is used by some OCI functions, like `OCIObjectNew()`, where it helps determine what type of object is created. It is also returned as the value of some attributes when an object is described; for example, querying the `OCI_ATTR_TYPECODE` attribute of a type returns an `OCITypeCode` value.

Table 3-10 lists the possible values for an `OCITypeCode`. There is a value corresponding to each Oracle datatype.

Table 3-10 OCITypeCode Values and Datatypes

Value | Datatype
--- | ---
`OCI_TYPECODE_REF` | REF
`OCI_TYPECODE_DATE` | DATE
`OCI_TYPECODE_TIMESTAMP` | TIMESTAMP
`OCI_TYPECODE_TIMESTAMP_TZ` | TIMESTAMP WITH TIME ZONE
`OCI_TYPECODE_TIMESTAMP_LTZ` | TIMESTAMP WITH LOCAL TIME ZONE
`OCI_TYPECODE_INTERVAL_YM` | INTERVAL YEAR TO MONTH
`OCI_TYPECODE_INTERVAL_DS` | INTERVAL DAY TO SECOND
`OCI_TYPECODE_REAL` | single-precision real
`OCI_TYPECODE_DOUBLE` | double-precision real
`OCI_TYPECODE_FLOAT` | floating-point
`OCI_TYPECODE_NUMBER` | Oracle NUMBER
`OCI_TYPECODE_BFLOAT` | BINARY_FLOAT
`OCI_TYPECODE_BDOUBLE` | BINARY_DOUBLE
`OCI_TYPECODE_DECIMAL` | decimal
`OCI_TYPECODE_OCTET` | octet
`OCI_TYPECODE_INTEGER` | integer
`OCI_TYPECODE_SMALLINT` | small int
`OCI_TYPECODE_RAW` | RAW
`OCI_TYPECODE_VARCHAR2` | variable string ANSI SQL, that is, VARCHAR2
`OCI_TYPECODE_VARCHAR` | variable string Oracle SQL, that is, VARCHAR
`OCI_TYPECODE_CHAR` | fixed-length string inside SQL, that is SQL CHAR
`OCI_TYPECODE_VARRAY` | variable-length array (varray)
`OCI_TYPECODE_TABLE` | multiset
`OCI_TYPECODE_CLOB` | character large object (CLOB)
`OCI_TYPECODE_BLOB` | binary large object (BLOB)
`OCI_TYPECODE_BFILE` | binary large object file (`BFILE`)
`OCI_TYPECODE_OBJECT` | named object type, or SYS.XMLType
`OCI_TYPECODE_NAMEDCOLLECTION` | Domain (named primitive type)

### Relationship Between SQLT and OCI_TYPECODE Values

Oracle recognizes two different sets of datatype code values. One set is distinguished by the `SQLT_` prefix, the other by the `OCI_TYPECODE_` prefix.

The `SQLT` typecodes are used by OCI to specify a datatype in a bind or define operation, enabling you to control data conversions between Oracle and OCI client applications. The `OCI_TYPECODE` types are used by Oracle's type system to reference or describe predefined types when manipulating or creating user-defined types.

In many cases there are direct mappings between `SQLT` and `OCI_TYPECODE` values. In other cases, however, there is not a direct one-to-one mapping. For example `OCI_TYPECODE_SIGNED16`, `OCI_TYPECODE_SIGNED32`, `OCI_TYPECODE_INTEGER`, `OCI_TYPECODE_OCTET`, and `OCI_TYPECODE_SMALLINT` are all mapped to the `SQLT_INT` type.

Table 3-11 illustrates the mappings between `SQLT` and `OCI_TYPECODE` types.

Table 3-11 OCI_TYPECODE to SQLT Mappings

Oracle Type System Typename | Oracle Type System Type | Equivalent SQLT Type
--- | --- | ---
`BFILE` | `OCI_TYPECODE_BFILE` | `SQLT_BFILE`
`BLOB` | `OCI_TYPECODE_BLOB` | `SQLT_BLOB`
`CHAR` | `OCI_TYPECODE_CHAR` (n) | `SQLT_AFC`(n) [note 1]
`CLOB` | `OCI_TYPECODE_CLOB` | `SQLT_CLOB`
`COLLECTION` | `OCI_TYPECODE_NAMEDCOLLECTION` | `SQLT_NCO`
`DATE` | `OCI_TYPECODE_DATE` | `SQLT_DAT`
`TIMESTAMP` | `OCI_TYPECODE_TIMESTAMP` | `SQLT_TIMESTAMP`
`TIMESTAMP WITH TIME ZONE` | `OCI_TYPECODE_TIMESTAMP_TZ` | `SQLT_TIMESTAMP_TZ`
`TIMESTAMP WITH LOCAL TIME ZONE` | `OCI_TYPECODE_TIMESTAMP_LTZ` | `SQLT_TIMESTAMP_LTZ`
`INTERVAL YEAR TO MONTH` | `OCI_TYPECODE_INTERVAL_YM` | `SQLT_INTERVAL_YM`
`INTERVAL DAY TO SECOND` | `OCI_TYPECODE_INTERVAL_DS` | `SQLT_INTERVAL_DS`
`FLOAT` | `OCI_TYPECODE_FLOAT` (b) | `SQLT_FLT` (8) [note 2]
`DECIMAL` | `OCI_TYPECODE_DECIMAL` (p) | `SQLT_NUM` (p, 0) [note 3]
`DOUBLE` | `OCI_TYPECODE_DOUBLE` | `SQLT_FLT` (8)
`BINARY_FLOAT` | `OCI_TYPECODE_BFLOAT` | `SQLT_BFLOAT`
`BINARY_DOUBLE` | `OCI_TYPECODE_BDOUBLE` | `SQLT_BDOUBLE`
`INTEGER` | `OCI_TYPECODE_INTEGER` | `SQLT_INT` (i) [note 4]
`NUMBER` | `OCI_TYPECODE_NUMBER` (p, s) | `SQLT_NUM` (p, s) [note 5]
`OCTET` | `OCI_TYPECODE_OCTET` | `SQLT_INT` (1)
`POINTER` | `OCI_TYPECODE_PTR` | <NONE>
`RAW` | `OCI_TYPECODE_RAW` | `SQLT_LVB`
`REAL` | `OCI_TYPECODE_REAL` | `SQLT_FLT` (4)
`REF` | `OCI_TYPECODE_REF` | `SQLT_REF`
OBJECT or `SYS.XMLType` | `OCI_TYPECODE_OBJECT` | `SQLT_NTY`
`SIGNED(8)` | `OCI_TYPECODE_SIGNED8` | `SQLT_INT` (1)
`SIGNED(16)` | `OCI_TYPECODE_SIGNED16` | `SQLT_INT` (2)
`SIGNED(32)` | `OCI_TYPECODE_SIGNED32` | `SQLT_INT` (4)
`SMALLINT` | `OCI_TYPECODE_SMALLINT` | `SQLT_INT` (i) [note 4]
`TABLE` [note 6] | `OCI_TYPECODE_TABLE` | <NONE>
`TABLE` (Indexed table) | `OCI_TYPECODE_ITABLE` | `SQLT_TAB`
`UNSIGNED(8)` | `OCI_TYPECODE_UNSIGNED8` | `SQLT_UIN` (1)
`UNSIGNED(16)` | `OCI_TYPECODE_UNSIGNED16` | `SQLT_UIN` (2)
`UNSIGNED(32)` | `OCI_TYPECODE_UNSIGNED32` | `SQLT_UIN` (4)
`VARRAY` [note 6] | `OCI_TYPECODE_VARRAY` | <NONE>
`VARCHAR` | `OCI_TYPECODE_VARCHAR` (n) | `SQLT_CHR` (n) [note 1]
`VARCHAR2` | `OCI_TYPECODE_VARCHAR2` (n) | `SQLT_VCS` (n) [note 1]

Notes:

1. n is the size of the string in bytes

2. These are floating point numbers, the precision is given in terms of binary digits. b is the precision of the number in binary digits.

3. This is equivalent to a `NUMBER` with no decimal places.

4. i is the size of the number in bytes, set as part of an OCI call.

5. p is the precision of the number in decimal digits; s is the scale of the number in decimal digits.

6. Can only be part of a named collection type.

## Definitions in oratypes.h

Throughout this guide you will see references to datatypes like `ub2` or `sb4`, or to constants like `UB4MAXVAL`. These types are defined in the `oratypes.h` header file, which is found in the `public` directory. The exact contents may vary according to the operating system you are using.

Note: The use of the datatypes in `oratypes.h` is the only supported means of supplying parameters to the OCI.